﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace Sinacor.Infra.Tools.ServiceRegistration.Data
{
    public class EndPoint : ResourceAccess
    {
        #region Methods

        public List<Entity.EndPoint> SelectEndPoint(Entity.EndPoint entity, bool addCharacterLike)
        {
            List<Entity.EndPoint> response = null;

            StringBuilder sql = new StringBuilder();

            sql.AppendLine("SELECT ");
            sql.AppendLine("    COD_ENDP, ");
            sql.AppendLine("    COD_SERV, ");
            sql.AppendLine("    NOME_PROT, ");
            sql.AppendLine("    NOME_COTR, ");
            sql.AppendLine("    DATA_ALTE, ");
            sql.AppendLine("    (SELECT MAX(DATA_ALTE) FROM TFWDMOD) DATA_ALTE_MAX ");
            sql.AppendLine("FROM ");
            sql.AppendLine("    TFWDENDP ");
            sql.AppendLine("WHERE ");
            sql.AppendLine("    (COD_ENDP = :COD_ENDP OR :COD_ENDP IS NULL) AND ");
            sql.AppendLine("    (COD_SERV = :COD_SERV OR :COD_SERV IS NULL) AND ");
            sql.AppendLine("    (UPPER(NOME_PROT) LIKE UPPER(:NOME_PROT) OR :NOME_PROT IS NULL) AND ");
            sql.AppendLine("    (UPPER(NOME_COTR) LIKE UPPER(:NOME_COTR) OR :NOME_COTR IS NULL) ");
            sql.AppendLine("ORDER BY NOME_PROT DESC");

            using (IDbCommand command = this.GetSqlStringCommand(sql.ToString()))
            {
                this.AddInParameter(command, "COD_ENDP", DbType.Int32, entity.EndPointId);
                this.AddInParameter(command, "COD_SERV", DbType.Int32, entity.Service.ServiceId);
                this.AddInParameter(command, "NOME_PROT", DbType.String, addCharacterLike == true ? AddCharacterLike(entity.Protocol.ProtocolName) : entity.Protocol.ProtocolName);
                this.AddInParameter(command, "NOME_COTR", DbType.String, addCharacterLike == true ? AddCharacterLike(entity.ContractName) : entity.ContractName);

                response = this.FindAll<Entity.EndPoint>(command,
                    (
                        reader => new Entity.EndPoint
                        {
                            EndPointId = reader.GetValue<int>("COD_ENDP"),
                            Service = entity.Service != null ? entity.Service : this.GetService(reader.GetValue<int>("COD_SERV")),
                            Protocol = this.GetProtocol(reader.GetValue<string>("NOME_PROT")),
                            ContractName = reader.GetValue<string>("NOME_COTR"),
                            DateChange = reader.GetValue<DateTime>("DATA_ALTE"),
                            IsItemDivergent = !reader.GetValue<DateTime>("DATA_ALTE").Equals(reader.GetValue<DateTime>("DATA_ALTE_MAX")),
                        }
                    )
                );
            }

            return response;
        }

        public Entity.EndPoint InsertEndPoint(Entity.EndPoint entity)
        {
            StringBuilder sql = new StringBuilder();

            sql.AppendLine("INSERT INTO TFWDENDP ( ");
            sql.AppendLine("    COD_ENDP, ");
            sql.AppendLine("    COD_SERV, ");
            sql.AppendLine("    NOME_COTR, ");
            sql.AppendLine("    NOME_PROT, ");
            sql.AppendLine("    DATA_ALTE ");
            sql.AppendLine("    ) VALUES ( ");
            sql.AppendLine("    :COD_ENDP, ");
            sql.AppendLine("    :COD_SERV, ");
            sql.AppendLine("    :NOME_COTR, ");
            sql.AppendLine("    :NOME_PROT, ");

            if (entity.DataOrigin == Entity.DataOrigin.Loader)
                sql.AppendLine("    :DATA_ALTE) ");
            else
                sql.AppendLine("    (SELECT MAX(DATA_ALTE) FROM TFWDMOD)) ");

            using (IDbCommand command = this.GetSqlStringCommand(sql.ToString()))
            {
                this.AddInParameter(command, "COD_ENDP", DbType.Int32, entity.EndPointId);
                this.AddInParameter(command, "COD_SERV", DbType.String, entity.Service != null ? entity.Service.ServiceId : null);
                this.AddInParameter(command, "NOME_COTR", DbType.String, entity.ContractName); 
                this.AddInParameter(command, "NOME_PROT", DbType.String, entity.Protocol.ProtocolName);

                if (entity.DataOrigin == Entity.DataOrigin.Loader)
                    this.AddInParameter(command, "DATA_ALTE", DbType.Date, entity.DateChange);

                this.ExecuteNonQuery(command);
            }

            return entity;
        }

        public void UpdateEndPoint(Entity.EndPoint entity)
        {
            StringBuilder sql = new StringBuilder();

            sql.AppendLine("UPDATE TFWDENDP SET ");
            sql.AppendLine("COD_SERV = :COD_SERV, ");
            sql.AppendLine("NOME_PROT = :NOME_PROT ");

            if (entity.DataOrigin == Entity.DataOrigin.Loader)
                sql.AppendLine(", DATA_ALTE = :DATA_ALTE ");

            sql.AppendLine("WHERE COD_ENDP = :COD_ENDP ");

            using (IDbCommand command = this.GetSqlStringCommand(sql.ToString()))
            {
                this.AddInParameter(command, "COD_ENDP", DbType.Int32, entity.EndPointId);
                this.AddInParameter(command, "COD_SERV", DbType.String, entity.Service != null ? entity.Service.ServiceId : null);
                this.AddInParameter(command, "NOME_PROT", DbType.String, entity.Protocol.ProtocolName);

                if (entity.DataOrigin == Entity.DataOrigin.Loader)
                    this.AddInParameter(command, "DATA_ALTE", DbType.Date, entity.DateChange);

                this.ExecuteNonQuery(command);
            }
        }

        public void DeleteEndPoint(Entity.EndPoint entity)
        {
            Method m = new Method();
            string sql = "DELETE FROM TFWDENDP WHERE COD_ENDP = :COD_ENDP";

            using (IDbCommand command = this.GetSqlStringCommand(sql))
            {
                this.AddInParameter(command, "COD_ENDP", DbType.Int32, entity.EndPointId);
                this.ExecuteNonQuery(command);
            }
        }

        public void DeleteEndPointByServiceId(Entity.EndPoint entity)
        {
            Method m = new Method();
            string sql = "DELETE FROM TFWDENDP WHERE COD_SERV = :COD_SERV";

            using (IDbCommand command = this.GetSqlStringCommand(sql))
            {
                this.AddInParameter(command, "COD_SERV", DbType.Int32, entity.Service.ServiceId);
                this.ExecuteNonQuery(command);
            }
        }

        public int GetNextEndPointId()
        {
            int retorno = 0;
            string sql = "SELECT SFWDENDP.NEXTVAL FROM DUAL";

            using (IDbCommand command = this.GetSqlStringCommand(sql))
            {
                retorno = Convert.ToInt32(this.ExecuteScalar(command));
            }

            return retorno;
        }

        private Entity.Service GetService(int service_id)
        {
            Service s = new Service();
            List<Entity.Service> responseList = null;
            Entity.Service response = null;

            if (service_id != 0)
            {
                responseList = s.SelectService(new Entity.Service { ServiceId = service_id }, false);

                if (responseList != null && responseList.Count > 0)
                    response = responseList[0];
            }

            return response;
        }

        private Entity.Protocol GetProtocol(string protocol_name)
        {
            Entity.Protocol response = null;

            if (!string.IsNullOrEmpty(protocol_name))
            {
                    response = new Entity.Protocol();
                    response.ProtocolName = protocol_name;
            }

            return response;
        }

        #endregion
    }
}
